<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@ page import="java.sql.*, javax.sql.*, java.io.*, javax.naming.*" %>
<html>
<head>
    <title>基于连接池和数据源访问数据库</title>
</head>
<body>
<h1>顾客信息</h1>
<%
    InitialContext context = new InitialContext();
    DataSource dataSource =
            (DataSource) context.lookup("jdbc/DSTest");
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    try {
        conn = dataSource.getConnection();
        stmt = conn.createStatement();
        stmt.execute("CREATE TABLE if not exists user (" +
                "      id varchar (13) unsigned NOT NULL AUTO_INCREMENT," +
                "      course varchar(20) NOT NULL," +
                "      score varchar(100) NOT NULL,"+
                "      primary key (id)"+
                "); "
        );
        stmt.execute("insert into user (id,course,score) values ('2017004','chinese','100');");
        stmt.execute("insert into user (id,course,score) values ('2017002','math','100');");
        stmt.execute("insert into user (id,course,score) values ('2017003','english','100');");
        rset = stmt.executeQuery("select * from user");
        if (rset.next()) {
%>
<table width="100%" border="1">
    <tr align="left">
        <th>学号</th><th>课程名</th><th>成绩</th>
    </tr>
    <%
        do {
    %>
    <tr>
        <td><%= rset.getString("id") %></td>
        <td><%= rset.getString("course") %></td>
        <td><%= rset.getString("score") %></td>
    </tr>
    <%
        } while (rset.next());
    %>
</table>
<%
} else {
%>
No results from query
<%
    }
} catch (SQLException e) {
%>
<%
        response.getWriter().println("输入数据已经存在了呢");
    } finally {
        if (rset != null) { rset.close(); }
        if (stmt != null) { stmt.close(); }
        if (conn != null) { conn.close(); }
        if (context != null) { context.close(); }
    }
%>
</body>
</html>

